package model;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by yvdedu.com on 2016/9/22.
 */
public class MySql extends Jdbc {
    private final int[] Level=new  int[]{0,100,300,700,1500,3100,6300};
    private final int SignOnExp=10;
    private final int SignOnGold=10;
    private final int Mulriple=2;

    public MySql() {
        super("com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1/novel?Unicode=true&characterEncoding=UTF-8", "root", "root");
    }

    /**
     * @param name
     * @param pwd
     * @return
     */
    public boolean addUser(String name,String pwd){
        return execUpdate("insert into user(name,pwd,time,exp,gold) values(?,?,now(),?,?)",name,pwd,0,0)>0;
    }

    public boolean setUserInfo(int user_id,String icon,String nickname,String sex,int age,String phone){
        if (execUpdate(
                "update user_info set " +
                        "icon=?," +
                        "nickname=?," +
                        "sex=?," +
                        "age=?," +
                        "phone=? where user_id=?",
                icon,
                nickname,
                sex,
                age,
                phone,
                user_id)>0)return true;
        return execUpdate(
                "insert into user_info set " +
                        "user_id=?," +
                        "icon=?," +
                        "nickname=?," +
                        "sex=?," +
                        "age=?," +
                        "phone=?",
                user_id,
                icon,
                nickname,
                sex,
                age,
                phone)>0;
    }

    private boolean setUser(User user){
        return  execUpdate("update user set " +
                "pwd=?," +
                "last_time=?," +
                "exp=?," +
                "gold=? " +
                "where id=?",
                user.getPwd(),
                user.getLast_time(),
                user.getExp(),
                user.getGold(),
                user.getId()
        )>0;
    }

    public User getUserById(int id){
        List<User> users=getUserByObject("select a.*,b.icon,b.nickname,b.sex,b.age,b.phone from user a left join user_info b on a.id=b.user_id where a.id=?",id);
        return users.size()>0?users.get(0):null;
    }





    public boolean setUserPwd(int id,String pwd){
        User user=getUserById(id);
        if (user==null)return false;
        user.setPwd(pwd);
        return setUser(user);
    }

    public boolean setUserPwd(String name,String pwd){
        User user=getUserByName(name);
        if (user==null)return false;
        user.setPwd(pwd);
        return setUser(user);
    }

    public boolean addUserGold(int id,int gold){
        User user=getUserById(id);
        if (user==null) return false;
        int lessGold=user.getGold()+gold;
        if (lessGold<0) return false;
        user.setGold(lessGold);
        return setUser(user);
    }

    public boolean addUserGold(String name,int gold){
        User user=getUserByName(name);
        if (user==null) return false;
        int lessGold=user.getGold()+gold;
        if (lessGold<0) return false;
        user.setGold(lessGold);
        return setUser(user);
    }

    private boolean addUserExp(int id,int exp){
        if (exp<=0)return false;
        User user=getUserById(id);
        if (user==null) return false;
        user.setExp(user.getExp()+exp);
        return setUser(user);
    }

    private boolean addUserExp(String name,int exp){
        if (exp<=0)return false;
        User user=getUserByName(name);
        if (user==null) return false;
        user.setExp(user.getExp()+exp);
        return setUser(user);
    }

    private boolean addManage(String name,String pwd,String role){
        return execUpdate("insert into manage(name,pwd,role) value(?,?,?)",name,pwd,role)>0;
    }

    public boolean addManage(String name,String pwd){
        return addManage(name,pwd,"0");
    }

    /**
     * 添加超级管理员
     * @param name
     * @param pwd
     * @return
     */
    public boolean addSuperManage(String name,String pwd){
        return addManage(name,pwd,"1");
    }

    public Manage getManageById(int id){
        List<Manage> manages=getManageByObject("select * from manage where id=?",id);
        return manages.size()>0?manages.get(0):null;
    }

    private boolean setManage(Manage manage){
        return execUpdate(
                "update manage set " +
                        "pwd=?," +
                        "role=? " +
                        "where id=?",
                manage.getPwd(),
                manage.getRole(),
                manage.getId()
        )>0;
    }

    public boolean upManage(String name ){
        Manage manage=getManageByName(name);
        if (manage==null) return  false;
        manage.setRole("1");
        return setManage(manage);
    }

    /**
     * 把普通管理员提升为超级管理员
     * @param id
     * @return
     */
    public boolean upManage(int id ){
        Manage manage=getManageById(id);
        if (manage==null) return  false;
        manage.setRole("1");
        return setManage(manage);
    }

    public boolean setManagePwd(int id,String pwd){
        Manage manage=getManageById(id);
        if (manage==null) return  false;
        manage.setPwd(pwd);
        return setManage(manage);
    }

    public boolean setManagePwd(String name,String pwd){
        Manage manage=getManageByName(name);
        if (manage==null) return  false;
        manage.setPwd(pwd);
        return setManage(manage);
    }

    private List<Chapter> getChaptersByObject(String sql,Object... o){
        return execQuery(new packEntity<List<Chapter>>() {
            public List<Chapter> pack(ResultSet resultSet) {
                List<Chapter> chapters=new ArrayList<Chapter>();
                try {
                    while (resultSet.next()){
                        chapters.add(new Chapter(
                                resultSet.getInt("id"),
                                resultSet.getInt("book_id"),
                                resultSet.getInt("chapter_order"),
                                resultSet.getString("name"),
                                resultSet.getString("time"),
                                resultSet.getString("path"),
                                resultSet.getString("role")
                        ));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return chapters;
            }
        },sql,o);
    }

    private List<Book> getBookByObject(String sql,Object... o){
        return execQuery(new packEntity<List<Book>>() {
            public List<Book> pack(ResultSet resultSet) {
                List<Book> books=new ArrayList<Book>();
                try {
                    while (resultSet.next()){
                        books.add(new Book(
                                resultSet.getInt("id"),
                                resultSet.getString("name"),
                                resultSet.getString("author"),
                                resultSet.getString("intro"),
                                resultSet.getString("image"),
                                resultSet.getString("type"),
                                resultSet.getString("time"),
                                resultSet.getInt("click"),
                                resultSet.getInt("score")
                        ));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return books;
            }
        },sql,o);
    }

    /**
     * 下载小说
     * @param book_id
     * @return
     */
    public StringBuffer DownLoad(int book_id,String path){
        StringBuffer All=new StringBuffer();
        getChaptersByBook_id(book_id).forEach(e->Joint(All,e,path));
        return All;
    }

    private void Joint(StringBuffer sb,Chapter chapter,String path){
        try {
            File file=new File(((path.endsWith("\\")||path.endsWith("/"))?path:(path+"\\"))+chapter.getPath());
            if (!file.exists())return;
            sb.append("\r\n"+chapter.getName()+"\r\n");
            InputStream i=new FileInputStream(file);
            Scanner s=new Scanner(i,"gb2312");
            StringBuffer temp=new StringBuffer();
            while (s.hasNextLine()){
                temp.append(s.nextLine());
            }
            //System.out.println(temp);
            Pattern p=Pattern.compile("(?s)(?<=document\\.write\\(').*(?='\\))");
            Matcher m=p.matcher(temp);
            while (m.find()){
                //System.out.println("aa"+m.group());
                String str=m.group().replaceAll("<p>","\r\n");
                sb.append(str);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }


    private int getCount(String sql,Object... o){
        return execQuery(new packEntity<Integer>() {
            public Integer pack(ResultSet resultSet) {
                int count= 0;
                try {
                    while (resultSet.next()){
                        count=resultSet.getInt("count");
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return count;
            }
        },sql,o);
    }



    public List<Book> getBooksBySearch(String search,int page){
        int pageCount=15;
        int startPage=(page-1)*pageCount;
        int endPage=startPage+pageCount;
        return getBookByObject("select a.*,b.score from book a left join (select book_id,avg(score) as score from opt group by book_id) b on a.id=b.book_id where a.name like ? or a.author like ? limit "+startPage+","+endPage,"%"+search+"%","%"+search+"%");
    }

    /**
     * 获取搜索到小说的数量
     * @param search
     * @return
     */
    public int getBooksCountBySearch(String search){
        return getCount("select count(*) as count from book a left join (select book_id,avg(score) as score from opt group by book_id) b on a.id=b.book_id where a.name like ? or a.author like ?","%"+search+"%","%"+search+"%");
    }

    public List<Book> getBooksByType(String type,int page){
        int pageCount=15;
        int startPage=(page-1)*pageCount;
        int endPage=startPage+pageCount;
        return getBookByObject("select a.*,b.score from book a left join (select book_id,avg(score) as score from opt group by book_id) b on a.id=b.book_id where a.type=? limit "+startPage+","+endPage,type);
    }

    /**
     * 某种类型小说的数量
     * @param type
     * @return
     */
    public int getBooksCountByType(String type){
        return getCount("select count(*) as count from book a left join (select book_id,avg(score) as score from opt group by book_id) b on a.id=b.book_id where a.type=?",type);
    }

    public List<Book> getBooks(int page){
        int pageCount=15;
        int startPage=(page-1)*pageCount;
        int endPage=startPage+pageCount;
        return getBookByObject("select a.*,b.score from book a left join (select book_id,avg(score) as score from opt group by book_id) b on a.id=b.book_id limit "+startPage+","+endPage);
    }

    /**
     * 小说总数量
     * @return
     */
    public int getBooksCount(){
        return getCount("select count(*) as count from book a left join (select book_id,avg(score) as score from opt group by book_id) b on a.id=b.book_id");
    }

    /**
     * 获取小说类型
     * @return 小说类型
     */
    public List<String> getTypes(){
        return execQuery(new packEntity<List<String>>() {
            @Override
            public List<String> pack(ResultSet resultSet) {
                List<String> types=new ArrayList<String>();
                try {
                    while (resultSet.next()){
                        types.add(resultSet.getString("type"));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return types;
            }
        },"select type from book group by type");
    }


    private List<User> getUserByObject(String sql,Object... o){
        return execQuery(new packEntity<List<User>>() {
            public List<User> pack(ResultSet resultSet) {
                List<User> users=new ArrayList<User>();
                try {
                    while (resultSet.next()){
                        users.add(new User(
                            resultSet.getInt("id"),
                            resultSet.getString("name"),
                            resultSet.getString("pwd"),
                            resultSet.getString("time"),
                            resultSet.getString("last_time"),
                            resultSet.getInt("exp"),
                            resultSet.getInt("gold"),
                            resultSet.getString("icon"),
                            resultSet.getString("nickname"),
                            resultSet.getString("sex"),
                            resultSet.getInt("age"),
                            resultSet.getString("phone")
                            ));}
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return users;
            }
        },sql,o);
    }

    private List<Manage> getManageByObject(String sql,Object... o){
        return execQuery(new packEntity<List<Manage>>() {
            public List<Manage> pack(ResultSet resultSet) {
                List<Manage> manages=new ArrayList<Manage>();
                try {
                    while (resultSet.next()){
                        manages.add(new Manage(
                                resultSet.getInt("id"),
                                resultSet.getString("name"),
                                resultSet.getString("pwd"),
                                resultSet.getString("role")
                        ));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return manages;
            }
        },sql,o);
    }

    /**
     * 获取书的章节信息
     * @param book_id
     * @return
     */
    public List<Chapter> getChaptersByBook_id(int book_id){
        return getChaptersByObject("select * from chapter where book_id=? order by chapter_order asc",book_id);
    }

    public User getUserByName(String name){
        List<User> users=getUserByObject("select a.*,b.icon,b.nickname,b.sex,b.age,b.phone from user a left join user_info b on a.id=b.user_id where a.name=?",name);
        return users.size()>0?users.get(0):null;
    }

    public Manage getManageByName(String name ){
        List<Manage> manages=getManageByObject("select * from manage where name=?",name);
        return manages.size()>0?manages.get(0):null;
    }

    public Book getBookInfoById(int id){
        List<Book> books=getBookByObject("select a.*,b.score from book a left join (select book_id,avg(score) as score from opt group by book_id) b on a.id=b.book_id where a.id=?",id);
        return books.size()>0?books.get(0):null;
    }


    /**
     * 获取点击量前10的小说
     * @return
     */
    public List<Book> getBookByClick(){
        return getBookByObject("select a.*,b.score from book a left join (select book_id,avg(score) as score from opt group by book_id) b on a.id=b.book_id order by a.click desc limit 0,8");
    }


    /**
     * 随机获取一些书
     * @return
     */
    public List<Book> getBookByRandom(){
        int count=getCount("select count(*) as count from book");
        if (count<11)return getBookByObject("select a.*,b.score from book a left join (select book_id,avg(score) as score from opt group by book_id) b on a.id=b.book_id");
        Set<Integer> rdm=new HashSet<Integer>();
        for(int i=0;rdm.size()<10||i<20;++i){
            rdm.add(new Random().nextInt(count));
        }
        StringBuffer sql=new StringBuffer("select a.*,b.score from book a left join (select book_id,avg(score) as score from opt group by book_id) b on a.id=b.book_id where a.id='0'");
        rdm.forEach(e->sql.append(" or a.id='"+e+"'"));
        List<Book> books=getBookByObject(sql.toString());
        return books.size()>0?books:null;
    }



    private boolean signOn(User user){
        int level=getLevel(user.getExp());
        SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
        Date last_time;
        int multiple=1;
        try {
            String lastTime=user.getLast_time();
            if (lastTime==null)throw new Exception("没有签到记录");
            last_time=df.parse(lastTime);
        } catch (Exception e) {
            System.out.println("没有签到记录");
            last_time=null;
        }
        if (last_time!=null){
            Calendar calendar=Calendar.getInstance();
            calendar.setTime(last_time);
            int less=Calendar.getInstance().get(Calendar.DAY_OF_YEAR)-calendar.get(Calendar.DAY_OF_YEAR);
            if(less==0)return false;
            if (less==1){
                multiple=Mulriple;
            }
        }
        user.setLast_time(df.format(new Date()));
        user.setExp(user.getExp()+SignOnExp*multiple);
        user.setGold(user.getGold()+SignOnGold*level*multiple);
        return setUser(user);
    }

    public boolean signOn(int id){
        User user=getUserById(id);
        if (user==null) return false;
        return signOn(user);
    }

    /**
     * 签到
     * @param name
     * @return
     */
    public boolean signOn(String name){
        User user=getUserByName(name);
        if (user==null) return false;
        return signOn(user);
    }

    public boolean addBook(String name,String author,String intro,String image,String type){
        return execUpdate(
                "insert into book set " +
                        "name=?," +
                        "author=?," +
                        "intro=?," +
                        "image=?," +
                        "type=?," +
                        "time=now()," +
                        "click=0",
                name,
                author,
                intro,
                image,
                type)>0;
    }

    private boolean setBook(Book book){
        return execUpdate(
                "update book set " +
                        "name=?," +
                        "author=?," +
                        "intro=?," +
                        "image=?," +
                        "click=? where id=?",
                book.getName(),
                book.getAuthor(),
                book.getIntro(),
                book.getImage(),
                book.getClick(),
                book.getId())>0;
    }

    /**
     * 增加点击量
     * @param chapter_id
     * @return
     */
    public boolean addClick(int chapter_id){
        Chapter chapter=getChapterById(chapter_id);
        Book book = getBookInfoById(chapter.getBook_id());
        if (book==null) return false;
        book.setClick(book.getClick()+1);
        return setBook(book);
    }

    /**
     * @param id
     * @param name
     * @param author
     * @param intro
     * @param image
     * @return
     */
    public boolean setBook(int id,String name,String author,String intro,String image){
        Book book = getBookInfoById(id);
        if (book==null) return false;
        book.setName(name);
        book.setAuthor(author);
        book.setIntro(intro);
        book.setImage(image);
        return setBook(book);
    }

    /**
     * @param book_id
     * @param name
     * @param path
     * @param role
     * @return
     */
    public boolean addChapter(int book_id,String name,String path,String role){
        return execUpdate(
                "insert into chapter set " +
                        "book_id=?," +
                        "name=?," +
                        "time=now()," +
                        "path=?," +
                        "role=?",
                book_id,
                name,
                path,
                role)>0;
    }

    private boolean setChapter(Chapter chapter){
        return execUpdate(
                "update chapter set " +
                        "book_id=?," +
                        "name=?," +
                        "path=?," +
                        "role=? where id=?",
                chapter.getBook_id(),
                chapter.getName(),
                chapter.getPath(),
                chapter.getRole(),
                chapter.getId()
        )>0;
    }

    public Chapter getChapterById(int id){
        List<Chapter> chapters=getChaptersByObject("select * from chapter where id=?",id);
        return chapters.size()>0?chapters.get(0):null;
    }

    public boolean setChapter(int id,String name,String path,String role){
        Chapter chapter=getChapterById(id);
        if (chapter==null)return false;
        chapter.setName(name);
        chapter.setPath(path);
        chapter.setRole(role);
        return setChapter(chapter);
    }

    /**
     * 改变章节阅读权限
     * @param id
     * @param role
     * @return
     */
    public boolean setChapterRole(int id,String role){
        Chapter chapter=getChapterById(id);
        if (chapter==null)return false;
        chapter.setRole(role);
        return setChapter(chapter);
    }

    private List<Opt> getOptionByObject(String sql, Object... o){
        return execQuery(new packEntity<List<Opt>>() {
            public List<Opt> pack(ResultSet resultSet) {
                List<Opt> opts =new ArrayList<Opt>();
                try {
                    while (resultSet.next()){
                        opts.add(new Opt(
                                resultSet.getInt("id"),
                                resultSet.getInt("book_id"),
                                resultSet.getInt("user_id"),
                                resultSet.getString("opt"),
                                resultSet.getString("time"),
                                resultSet.getString("score")
                        ));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return opts;
            }
        },sql,o);
    }

    /**
     * 获取小说评价
     * @param book_id
     * @return
     */
    public List<Opt> getOptionsByBookId(int book_id){
        return getOptionByObject("select * from opt where book_id=?",book_id);
    }

    /**
     * 添加评价
     * @param book_id
     * @param user_id
     * @param opt
     * @param score
     * @return
     */
    public boolean addOption(int book_id,int user_id,String opt,int score){
        return execUpdate(
                "insert into opt set " +
                        "book_id=?," +
                        "user_id=?," +
                        "opt=?," +
                        "time=now()," +
                        "score=?",
                book_id,
                user_id,
                opt,
                score)>0;
    }

    /**
     * 删除评价
     * @param id
     * @return
     */
    private boolean deleteOption(int id){
        return execUpdate("delete from opt where id=?",id)>0;
    }

    private List<Tab> getTabByObject(String sql,Object... o){
        return execQuery(new packEntity<List<Tab>>() {
            public List<Tab> pack(ResultSet resultSet) {
                List<Tab> tabs=new ArrayList<Tab>();
                try {
                    while (resultSet.next()){
                        tabs.add(new Tab(
                                resultSet.getInt("id"),
                                resultSet.getInt("user_id"),
                                resultSet.getInt("chapter_id"),
                                resultSet.getString("buy"),
                                resultSet.getString("collect"),
                                resultSet.getString("time")
                        ));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return tabs;
            }
        },sql,o);
    }

    /**
     * 获取用户已经购买的章节
     * @param user_id
     * @return
     */
    public List<Chapter> getBuyChapterByUserId(int user_id){
        return getChaptersByObject("select a.* from chapter a right join tab b on a.id=b.chapter_id where b.user_id=? and b.buy=?",user_id,"1");
    }

    /**
     * 获取用户已经收藏的章节
     * @param user_id
     * @return
     */
    public List<Book> getCollectBookByUserId(int user_id){
        return getBookByObject("select a.* from book a right join (select a.book_id from chapter a right join tab b on a.id=b.chapter_id where b.user_id=? and b.collect=? group by a.book_id) b on a.id=b.book_id",user_id,"1");
    }

    /**
     * 获取用户最近查看的章节
     * @param user_id
     * @return
     */
    public List<Chapter> getHisChapterByUserId(int user_id){
        return getChaptersByObject("select a.* from chapter a right join tab b on a.id=b.chapter_id where b.user_id=? and b.time <> order by b.time desc limit 0,20",user_id);
    }

    public boolean buyChapter(int user_id,int chapter_id){
        return execUpdate(
                "insert into chapter set " +
                        "user_id=?," +
                        "chapter_id=?," +
                        "buy=?",
                user_id,
                chapter_id,"1")>0;
    }

    public boolean colChapter(int user_id,int book_id){
        List<Chapter> chapters=getChaptersByBook_id(book_id);
        return execUpdate(
                "insert into chapter set " +
                        "user_id=?," +
                        "chapter_id=?," +
                        "collect=?",
                user_id,
                chapters.get(0).getId(),"1")>0;
    }

    public boolean hisChapter(int user_id,int chapter_id){
        return execUpdate(
                "insert into chapter set " +
                        "user_id=?," +
                        "chapter_id=?," +
                        "time=now()",
                user_id,
                chapter_id)>0;
    }

    public int getLevel(int exp){
        for (int i=0;i<Level.length;++i){
            if (exp<Level[i])return i;
        }
        return 7;
    }
}
